package com.matt.repository.RoleJob;

import com.matt.domain.roleJob.RoleJobFinish;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;


public interface RoleJobFinishRepository extends JpaRepository<RoleJobFinish, String>, JpaSpecificationExecutor<RoleJobFinish> {


    @Query(value = "SELECT *  FROM tb_role_job_finish   WHERE staffId = ?1 AND roleCode = ?2 and DATE_FORMAT(finishTime, '%Y-%m-%d')=?3 ",nativeQuery = true)
    List<RoleJobFinish> selectFinishJob(String staffId,String roleCode,String today);

    @Query(value = "SELECT *  FROM tb_role_job_finish   WHERE staffId = ?1 AND roleCode = ?2 and DATE_FORMAT(finishTime, '%Y-%m-%d')=?3 and jobId =?4 ",nativeQuery = true)
    RoleJobFinish selectFinishByJobIdAndFinishTime(String staffId,String roleCode,String today,String jobId);

    @Query(value = "SELECT a.* FROM tb_role_job_finish a,tb_role_job b WHERE a.jobId = b.id and b.remark='lastweekofmonth' AND a.staffId = ?1 AND a.roleCode = ?2 AND DATE_FORMAT(a.finishTime, '%Y-%m') = ?3 ",nativeQuery = true)
    List<RoleJobFinish> selectMonthFinishJob(String staffId,String roleCode,String month);

    @Query(value = "SELECT a.* FROM tb_role_job_finish a,tb_role_job b WHERE a.jobId = b.id and b.remark='december' AND a.staffId = ?1 AND a.roleCode = ?2 AND DATE_FORMAT(a.finishTime, '%Y') = ?3 ",nativeQuery = true)
    List<RoleJobFinish> selectYearFinishJob(String staffId,String roleCode,String year);


}
